import mysql.connector
from datetime import datetime, timedelta
import logging
from elasticsearch import Elasticsearch
from elasticsearch.helpers import bulk
from django.conf import settings

# logging.basicConfig(level=logging.INFO)
logger = logging.getLogger('django')


class CourseSync:
    """
    课程数据同步类,全量同步到es
    """
    def __init__(self):
        # MySQL配置
        self.mysql_config = {
            'host': settings.DATABASES['default']['HOST'],
            'user': settings.DATABASES['default']['USER'],
            'password': settings.DATABASES['default']['PASSWORD'],
            'database': settings.DATABASES['default']['NAME']
        }
        # ES配置
        self.es = Elasticsearch(['http://localhost:9200'])

    def get_mysql_connection(self):
        return mysql.connector.connect(**self.mysql_config)

    def fetch_course_data(self, cursor, course_id=None):
        """获取课程数据的SQL查询"""
        base_sql = """
        SELECT 
            c.id, c.name, c.brief, c.course_img, c.students, c.lessons,
            c.pub_lessons, c.course_type, c.original_price, c.price,
            c.status, c.level, c.pub_date,
            t.id as teacher_id, t.name as teacher_name, t.title as teacher_title,
            GROUP_CONCAT(DISTINCT cc.id) as category_ids,
            GROUP_CONCAT(DISTINCT nc.name) as nav_category_names,
            GROUP_CONCAT(DISTINCT ct.name) as tag_names
        FROM tb_course c
        LEFT JOIN tb_teacher t ON c.teacher_id = t.id
        LEFT JOIN tb_course_categories cc ON c.id = cc.course_id
        LEFT JOIN tb_course_nav_relation cnr ON c.id = cnr.course_id
        LEFT JOIN tb_nav_category nc ON cnr.nav_category_id = nc.id
        LEFT JOIN tb_course_tag_relation ctr ON c.id = ctr.course_id
        LEFT JOIN tb_course_tag ct ON ctr.tag_id = ct.id
        WHERE c.is_deleted = 0
        """

        if course_id:
            base_sql += " AND c.id = %s"
            cursor.execute(base_sql + " GROUP BY c.id", (course_id,))
        else:
            cursor.execute(base_sql + " GROUP BY c.id")

        return cursor.fetchall()

    def prepare_es_doc(self, course_data):
        """将MySQL数据转换为ES文档格式"""
        return {
            'id': course_data[0],
            'name': course_data[1],
            'brief': course_data[2],
            'course_img': course_data[3],
            'students': course_data[4],
            'lessons': course_data[5],
            'pub_lessons': course_data[6],
            'course_type': course_data[7],
            'original_price': float(course_data[8]),
            'price': float(course_data[9]),
            'status': course_data[10],
            'level': course_data[11],
            'pub_date': course_data[12].isoformat() if course_data[12] else None,
            'teacher_id': course_data[13],
            'teacher_name': course_data[14],
            'teacher_title': course_data[15],
            'category_ids': [int(x) for x in course_data[16].split(',')] if course_data[16] else [],
            'nav_category_names': course_data[17].split(',') if course_data[17] else [],
            'tag_names': course_data[18].split(',') if course_data[18] else []
        }

    def full_sync(self):
        """全量同步"""
        try:
            # 创建新索引
            temp_index = 'course_index'
            self.es.indices.create(index=temp_index, ignore=400, body={
                "settings": {
                    "number_of_shards": 1,
                    "number_of_replicas": 1
                },
                "mappings": {
                    "properties": {
                        "id": {"type": "integer"},
                        "name": {"type": "text", "analyzer": "ik_max_word", "fields": {"keyword": {"type": "keyword"}}},
                        "brief": {"type": "text", "analyzer": "ik_max_word"},
                        "course_img": {"type": "keyword"},
                        "students": {"type": "integer"},
                        "lessons": {"type": "integer"},
                        "pub_lessons": {"type": "integer"},
                        "course_type": {"type": "integer"},
                        "original_price": {"type": "double"},
                        "price": {"type": "double"},
                        "status": {"type": "integer"},
                        "level": {"type": "integer"},
                        "pub_date": {"type": "date"},
                        "teacher_id": {"type": "integer"},
                        "teacher_name": {"type": "text", "analyzer": "ik_max_word",
                                         "fields": {"keyword": {"type": "keyword"}}},
                        "teacher_title": {"type": "keyword"},
                        "category_ids": {"type": "integer"},
                        "nav_category_names": {"type": "keyword"},
                        "tag_names": {"type": "keyword"}
                    }
                }
            })

            conn = self.get_mysql_connection()
            cursor = conn.cursor()

            # 批量获取数据并同步到ES
            batch_size = 1000
            offset = 0

            while True:
                cursor.execute(f"SELECT id FROM tb_course WHERE is_deleted = 0 LIMIT {offset}, {batch_size}")
                course_ids = cursor.fetchall()

                if not course_ids:
                    break

                actions = []
                for (course_id,) in course_ids:
                    course_data = self.fetch_course_data(cursor, course_id)
                    if course_data:
                        doc = self.prepare_es_doc(course_data[0])
                        actions.append({
                            '_index': temp_index,
                            '_id': doc['id'],
                            '_source': doc
                        })

                if actions:
                    bulk(self.es, actions)

                offset += batch_size
                logger.info(f"Synced {offset} courses")

            # 获取当前的courses别名指向的索引
            try:
                current_index = list(self.es.indices.get_alias(name='course_index_alias').keys())[0]
                # 将别名指向新索引
                self.es.indices.update_aliases(body={
                    "actions": [
                        {"remove": {"index": current_index, "alias": "course_index_alias"}},
                        {"add": {"index": temp_index, "alias": "course_index_alias"}}
                    ]
                })
                # 删除旧索引
                self.es.indices.delete(index=current_index)
            except Exception as e:
                # 如果是第一次创建，直接添加别名
                self.es.indices.update_aliases(body={
                    "actions": [
                        {"add": {"index": temp_index, "alias": "course_index_alias"}}
                    ]
                })

        except Exception as e:
            logger.error(f"Full sync failed: {str(e)}")
            raise
        finally:
            cursor.close()
            conn.close()

    def sync_single_course(self, course_id):
        """同步单个课程"""
        try:
            conn = self.get_mysql_connection()
            cursor = conn.cursor()

            # 获取课程数据
            course_data = self.fetch_course_data(cursor, course_id)

            if course_data:
                doc = self.prepare_es_doc(course_data[0])
                self.es.index(
                    index='course_index_alias',
                    id=doc['id'],
                    body=doc
                )
                logger.info(f"Successfully synced course {course_id}")
            else:
                logger.warning(f"Course {course_id} not found or is deleted")

        except Exception as e:
            logger.error(f"Error syncing course {course_id}: {str(e)}")
            raise
        finally:
            cursor.close()
            conn.close()
